﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using CaiyinSoft.NetGather.Protocol;

namespace CaiyinSoft.NetGather.NetGatherLib.Publisher
{
    [Serializable]
    public class SQLHTMLPublisher : HTMLPublisherBase
    {

        public string ConnectionStr { get; set; }
        public string ToExecuteSQL { get; set; }

        public SQLHTMLPublisher():base()
        {
            _InitParams();
        }
        private void _InitParams()
        {
            ConnectionStr = "";
            ToExecuteSQL = "";
        }

        public override void Publish(Dictionary<int, string> HTMLList, IHTMLAnalyzer HTMLAnalyzer)
        {
            if (string.IsNullOrEmpty(ToExecuteSQL))
                return;

            //从类似 {@delFlag(text3)} 的字符串获取delFlag
            //string pattern = @"(?<=[(,]\s*{@)\w+(?=(\(\w+\))?}\s*[,)])";// @"(?<=[(,]\s*{@)\w+(?=}\s*[,)])";
            // 从SQL语句中获取到类似 {@delFlag(text3)} 的字符串
            string pattern = @"(?<=[(,]\s*){@\w+(\(\w+\))?}(?=\s*[,)])";

            Regex regex = new System.Text.RegularExpressions.Regex(pattern);
            MatchCollection matches = regex.Matches(ToExecuteSQL);
            if (matches.Count == 0)
            {
                _ExecuteNonQuery(ToExecuteSQL, null);
            }

            Dictionary<string, string> keys = new Dictionary<string, string>();
            for (int i = 0; i < matches.Count; i++)
            {
                keys.Add(_GetKey(matches[i].Value), _GetDBType(matches[i].Value));
            }

            Dictionary<string, StringBuilder> paramDic = new Dictionary<string, StringBuilder>();
            foreach (var html in HTMLList)
            {
                Dictionary<string, List<FilterResult>> result = GetAnalyzer(html.Value, HTMLAnalyzer);
                foreach (var kv in result)
                {
                    if (kv.Value == null)
                        continue;

                    StringBuilder sb = new StringBuilder();
                    List<FilterResult> results = kv.Value;
                    results.Sort(FilterResult.Compare);
                    foreach (var res in results)
                    {
                        if (sb.Length == 0)
                            sb.AppendFormat("{0}", res.HTMLResult);
                        else
                            sb.AppendFormat("{0}{1}", Environment.NewLine, res.HTMLResult);
                    }

                    if (!paramDic.ContainsKey(kv.Key))
                    {
                        paramDic.Add(kv.Key, sb);
                    }
                    else 
                    {
                        paramDic[kv.Key].Append(sb);
                    }
                }
            }

            //Dictionary<string, List<FilterResult>> analyzerResult = GetAnalyzer(HTMLList, HTMLAnalyzer);

            List<SqlParameter> Params = new List<SqlParameter>();
            foreach (var kv in keys)
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = string.Format("@{0}", kv.Key);
                p.SqlDbType = _GetSqlDbType(kv.Value);
                //List<FilterResult> results = analyzerResult[kv.Key];
                //results.Sort(FilterResult.Compare);
                //StringBuilder sb = new StringBuilder();
                //foreach (var res in results)
                //{
                //    sb.AppendFormat("{0}{1}", res.HTMLResult, Environment.NewLine);
                //}
                p.Value = paramDic[kv.Key].ToString(); // sb.ToString();// _CreateParamValue(HTMLFilters, kv.Value);
                Params.Add(p);
            }

            _ExecuteNonQuery(_CreateExecSQL(), Params);
        }

        public override void PublishListData(Dictionary<int, string> HTMLList, IHTMLAnalyzer HTMLAnalyzer)  //List<IHTMLFilter> HTMLFilters)
        {
            if ( string.IsNullOrEmpty( ToExecuteSQL))
                return;

            //从类似 {@delFlag(text3)} 的字符串获取delFlag
            //string pattern = @"(?<=[(,]\s*{@)\w+(?=(\(\w+\))?}\s*[,)])";// @"(?<=[(,]\s*{@)\w+(?=}\s*[,)])";
            // 从SQL语句中获取到类似 {@delFlag(text3)} 的字符串
            string pattern = @"(?<=[(,]\s*){@\w+(\(\w+\))?}(?=\s*[,)])";

            Regex regex = new System.Text.RegularExpressions.Regex(pattern);
            MatchCollection matches = regex.Matches(ToExecuteSQL);
            if (matches.Count == 0)
            {
                _ExecuteNonQuery(ToExecuteSQL,null);
            }

            Dictionary<string, string> keys = new Dictionary<string, string>();
            for (int i=0;i<matches.Count;i++)
            {
                keys.Add(_GetKey(matches[i].Value), _GetDBType(matches[i].Value));
            }

            Dictionary<string, List<FilterResult>> analyzerResult = GetAnalyzer(HTMLList, HTMLAnalyzer);

            foreach (var kv in keys)
            {
                List<FilterResult> results = analyzerResult[kv.Key];
                results.Sort(FilterResult.Compare);
                foreach (var res in results)
                {
                    List<SqlParameter> Params= new List<SqlParameter>();
                    SqlParameter p = new SqlParameter();
                    p.ParameterName = string.Format("@{0}", kv.Key);
                    p.SqlDbType = _GetSqlDbType(kv.Value);
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("{0}", res.HTMLResult);
                    p.Value = sb.ToString();// _CreateParamValue(HTMLFilters, kv.Value);
                    Params.Add(p);
                    _ExecuteNonQuery(_CreateExecSQL(), Params);
                }
            }
        }

        private string _CreateParamValue(Dictionary<int, List<IHTMLFilter>> HTMLFilters, string Key)
        {
            System.Text.StringBuilder sb = new StringBuilder();
            HTMLFilters.OrderBy(m => m.Key);
            foreach (var list in HTMLFilters)
            {
                List<IHTMLFilter> result = list.Value.FindAll ((e) => e.OutputKey.CompareTo(Key) == 0);
                if (result != null)
                {
                    foreach (var filter in result)
                    {
                        sb.AppendFormat("{0}{1}", filter.FilterHTML(), Environment.NewLine);
                    }
                }
            }

            return sb.ToString();
        }

        private string _CreateExecSQL()
        {
            //(?<=[(,]\s*){@\w+(\(\w+\))?}(?=\s*[,)])
            // 从SQL语句中获取到类似 {@delFlag(text3)} 的字符串
            string pattern = @"(?<=[(,]\s*){@\w+(\(\w+\))?}(?=\s*[,)])";//  @"((?<=[(,]\s*){@\w+}(?=\s*[,)])";
            Regex regex = new System.Text.RegularExpressions.Regex(pattern);
            MatchCollection matches = regex.Matches(ToExecuteSQL);
            StringBuilder sb = new StringBuilder(ToExecuteSQL);

            string t="";
            for (int i = 0; i < matches.Count; i++)
            {
                t=matches[i].Value;
                sb.Replace(t, string.Format("@{0}",_GetKey(t)));
            }
            return sb.ToString();
        }

        private string _GetKey(string Str)
        { 
        //(?<={@)\w+(?=(\(\w+\))?}\s*[,)])
            string pattern = @"(?<={@)\w+(?=(\(\w+\))?})";
            Regex regex = new System.Text.RegularExpressions.Regex(pattern);
            Match match = regex.Match(Str);
            if (match.Success)
                return match.Value;
            else
                return "";
        }

        private string _GetDBType(string Str)
        {
            string pattern = @"(?<={@\w+\()\w+(?=\)})";
            Regex regex = new System.Text.RegularExpressions.Regex(pattern);
            Match match = regex.Match(Str);
            if (match.Success)
                return match.Value;
            else
                return "NVarChar";
        }

        private SqlDbType _GetSqlDbType(string type)
        {
            string[] types = new string[] {"BigInt",	"Binary",	"Bit",	"Char",	"DateTime",	"Decimal",	"Float",	"Image",	"Int",	"Money",	"NChar",	"NText",	"NVarChar",	"Real",	"UniqueIdentifier",	"SmallDateTime",	"SmallInt",	"SmallMoney",	"Text",	"Timestamp",	"TinyInt",	"VarBinary",	"VarChar",	"Variant",	"Xml",	"Udt",	"Structured",	"Date",	"Time",	"DateTime2",	"DateTimeOffset"};
            string t= types.First(m=>{return m.ToLower().CompareTo(type.ToLower())==0;});
            string typeName = string.Format("{0}", t);
            SqlDbType result = (SqlDbType)Enum.Parse(typeof(SqlDbType), typeName);
            return result;
        }

        private bool _ExecuteNonQuery(string SQL,List<SqlParameter> Params)
        {
            int affrow = 0;
            using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(ConnectionStr))
            {
                sqlConnection.Open();
                System.Data.SqlClient.SqlCommand cmd = new SqlCommand(SQL, sqlConnection);
                if (Params!=null)
                {
                    cmd.Parameters.AddRange(Params.ToArray());
                }
                affrow = cmd.ExecuteNonQuery();
                cmd.Dispose();
                sqlConnection.Close();
            }

            return affrow > 0;
        }

        [NonSerialized]
        private SQLHTMLPublisherForm _editform;
        public override InnerFormBase GetEditForm()
        {
            if (_editform == null)
            {
                _editform = new SQLHTMLPublisherForm();
                _editform.InfoSaveEventHandler += (sender, args) =>
                {
                    this.ConnectionStr = _editform.ConnectionStr;
                    this.ToExecuteSQL = _editform.ToExecuteSQL;
                };
            }
            _editform.HTMLPublisher = this;
            return _editform;
        }

    }
}
